import mysql.connector as MySQLdb
import sys
sys.path.insert(0,'../.')
import config

mysql_user = "root"
mysql_password = "your_password"
mysql_host = "127.0.0.1"
db_name = "auth_simple"
table_name = "User"

def create_db(mysql_user=mysql_user, mysql_password=mysql_password ,mysql_host=mysql_host, db_name=db_name):
    try:
        conn = MySQLdb.connect(user=mysql_user, host=mysql_host, password=mysql_password)
        print('成功连接mysql数据库！')
        try:
            cur = conn.cursor()
            cur.execute("CREATE DATABASE IF NOT EXISTS "+db_name+" ;")
            conn.commit()
            print("数据库创建成功！")
        finally:
            conn.close()
            print("关闭连接")
    except:
        print("无法连接数据库")

def create_table(mysql_user=mysql_user, mysql_password=mysql_password ,mysql_host=mysql_host,table_name=table_name, db_name=db_name):
    try:
        conn = MySQLdb.connect(user=mysql_user, host=mysql_host, password=mysql_password,  database=db_name)
        print('成功连接mysql数据库！')
        try:
            cur = conn.cursor()
            cur.execute("""CREATE TABLE IF NOT EXISTS %s (id INT AUTO_INCREMENT PRIMARY KEY,
                                                                              email varchar(255), 
                                                                              name varchar(255),
                                                                              password varchar(255) 
                                                                              );""" %(table_name))
            conn.commit()
            print("成功创建数据库表")
        finally:
            conn.close()
            print("关闭连接")

    except:
        print("无法连接数据库")

def insert_row(email, name, password, user_name=mysql_user, mysql_password=mysql_password, host_name=mysql_host, db_name=db_name, table_name=table_name):
    conn = MySQLdb.connect(user=user_name, password=mysql_password, host=host_name, database=db_name)
    try:
        cursor = conn.cursor()
        cursor.execute("""insert into %s (email, name, password) values ('%s', '%s', '%s'); """ %(table_name, email, name, password))
        conn.commit()
    finally:
        conn.close()
    print('成功插入记录')


def import_data(query, mysql_user=mysql_user, mysql_password=mysql_password, mysql_host=mysql_host):
    try:
        conn = MySQLdb.connect(user=mysql_user, host=mysql_host, password=mysql_password)
        print('成功连接mysql数据库！')
        print(query)
    except:
        print("无法连接数据库")
    try:
        cur = conn.cursor()
        cur.execute(query)
        results = cur.fetchall()
        data = pd.DataFrame(list(results), columns=[row[0] for row in cur.description]).reset_index(drop=True)
        print(data)
    finally:
        conn.close()
    return (data)
